package cn.lunatic.db;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.apache.log4j.Logger;

import cn.lunatic.bean.SqlStatement;
import cn.lunatic.cache.Config;
import cn.lunatic.pub.DataTable;

/**
 * 
 * @Description:sql执行类，不
 * @author ganfeng
 * @date 2014-3-10 下午12:09:35
 */
public final class SqlExecutor implements Serializable {
	private static final long serialVersionUID = -6231406369744037117L;

	private final static Logger logger = Logger.getLogger(SqlExecutor.class);// 获取
	private final boolean autoCommit;
	private final DBSource dbSource;
	private Connection conn;

	/**
	 * 执行的sql语句对应配置的id
	 */
	private String sqlId;
	/**
	 * 执行sql语句所需要的参数
	 */
	private List<String> params;

	public SqlExecutor() {
		this(false);
	}

	public SqlExecutor(boolean autoCommit) {
		
		this.autoCommit = autoCommit;
		this.dbSource = Config.getDbSource();
		this.conn = dbSource.getConn(this.autoCommit);
	}

	/**
	 * 获取数据库连接
	 * 
	 * @Description:
	 * @author ganfeng
	 * @date 2014-3-10 下午03:32:42
	 * @return
	 */
	public Connection getConn() {
		return conn;
	}

	/**
	 * 关闭数据库连接
	 * 
	 * @Description:
	 * @author ganfeng
	 * @date 2014-3-10 下午03:32:53
	 */
	public void closeConn() {
		try {
			conn.close();
			conn = null;
		} catch (SQLException e) {
		}
	}

	/**
	 * 
	 * @Description:执行查询语句
	 * @author ganfeng
	 * @date 2014-3-10 下午03:42:16
	 * @param statement
	 * @return
	 */
	public DataTable query(SqlStatement statement) {
		sqlId = statement.getSqlId();
		params = statement.getParams();
		return query(statement.getSql());
	}

	/**
	 * @Description:执行查询语句
	 * @author ganfeng
	 * @date 2014-3-10 下午03:49:44
	 * @param sql
	 *            要执行的sql
	 * @return
	 */
	private DataTable query(String sql) {
		long sTime = System.currentTimeMillis();
		DataTable table = new DataTable();
		try {
			if (autoCommit) {
				conn = dbSource.getConn();
			}
			PreparedStatement preStmt = null;
			conn.setAutoCommit(autoCommit);
			preStmt = conn.prepareStatement(sql);
			preStmt.setMaxRows(dbSource.getMaxRecord());
			preStmt.setQueryTimeout(dbSource.getTimeOut());
			setSqlParameter(preStmt, params);

			ResultSet rs = preStmt.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int numCols = rsmd.getColumnCount();

			boolean more = rs.next();
			while (more) {
				table.addRow();
				for (int i = 1; i <= numCols; i++) {
					String name = rsmd.getColumnName(i).toLowerCase();
					String value = rs.getString(i);
					if (value == null) {
						value = "";
					} else {
						int type = rsmd.getColumnType(i);
						if (type == java.sql.Types.TIMESTAMP
								|| type == java.sql.Types.DATE
								|| type == java.sql.Types.TIME) { // 日期类型返回的是
																	// yyyy-mm-dd
																	// hh24:mi:ss.d，主要转换成yyyy-mm-dd
																	// hh24:mi:ss
							if (value.length() > 19) {
								value = value.substring(0, 19);
							}
						}
					}
					table.setValueLast(name, value);
				}
				more = rs.next();
			}
			rs.close();
			preStmt.close();
			logSqlData("SQL", sql, params, sTime, table.getRowCount(), null);
		} catch (Throwable e) {
			logSqlData("SQL", sql, params, sTime, 0, e);
		} finally {
			if (autoCommit) {
				closeConn();
			}
		}
		return table;
	}

	/**
	 * 
	 * @Description:执行更新语句
	 * @author ganfeng
	 * @date 2014-3-10 下午03:43:17
	 * @param statement
	 * @return 执行语句影响的条数
	 */
	public int update(SqlStatement statement) {
		sqlId = statement.getSqlId();
		params = statement.getParams();
		return update(statement.getSql(), false);
	}

	/**
	 * @Description:执行更新语句
	 * @author ganfeng
	 * @date 2014-3-10 下午05:21:04
	 * @param sql
	 * @return 执行语句影响的条数
	 */
	private int update(String sql , boolean returnGenerateKey) {
		long sTime = System.currentTimeMillis();
		int count = 0;
		PreparedStatement preStmt = null;
		try {
			if (autoCommit) {
				conn = dbSource.getConn();
			}
			conn.setAutoCommit(autoCommit);
			if (returnGenerateKey){
				preStmt = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			}else{
				preStmt = conn.prepareStatement(sql);
			}
			preStmt.setMaxRows(dbSource.getMaxRecord());
			preStmt.setQueryTimeout(dbSource.getTimeOut());
			setSqlParameter(preStmt, params);
			if (returnGenerateKey){
				preStmt.executeUpdate();
				ResultSet retKey = preStmt.getGeneratedKeys();
				if (retKey != null){
					if (retKey.next()){
						count = retKey.getInt(1);
					}
					retKey.close();
				}
			}else{
				count = preStmt.executeUpdate();
			}
			logSqlData("SQL", sql, params, sTime, count, null);
		} catch (Throwable e) {
			count = -1;
			logSqlData("SQL", sql, params, sTime, count, e);
			if (autoCommit) {
				try {
					conn.rollback();
				} catch (SQLException e1) {
					logger.info("回滚数据库失败...",e);
				}
			}
		} finally {
			try {
				preStmt.close();
			} catch (SQLException e) {
			}
			if (autoCommit) {
				closeConn();
			}
		}
		return count;
	}

	/**
	 * 
	 * @Description:执行更新语句 并返回自增列
	 * @author ganfeng
	 * @date 2014-3-10 下午03:45:24
	 * @param statement
	 * @return
	 */
	public int execInsertReturnKey(SqlStatement statement) {
		sqlId = statement.getSqlId();
		params = statement.getParams();
		return update(statement.getSql(), true);
	}

	/**
	 * 
	 * @Description:设置sql语句的参数
	 * @author ganfeng
	 * @date 2014-3-10 下午03:52:21
	 * @param preStmt
	 * @param params
	 * @return 参数的数量
	 */
	private int setSqlParameter(PreparedStatement preStmt, List<String> params) {
		if (params == null) {
			return 0;
		}
		int size = params.size();
		for (int index = 0; index < size; index++) {
			String param = params.get(index);
			index++; // 数据库的下标比java的下标值大1
			try {
				preStmt.setString(index, param);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			index--; // 从数据库的下标恢复为java下标
		}
		return size;
	}

	/**
	 * 打印sql语句和参数到日志
	 * 
	 * @param sql
	 *            ： sql语句
	 * @param params
	 *            ： sql 参数
	 * @param sTime
	 *            ： sql语句开始时间
	 * @param rows
	 *            ： 笔数
	 * @param e
	 *            ： 异常
	 */
	private void logSqlData(String name, String sql, List<String> params,
			long sTime, int rows, Throwable e) {
		String sqlLine = (e == null ? "执行" : "数据库SQL错误") + name + " [" + sqlId + "] [" + sql + " ]";
		String paramsLine = "";
		if (params == null || params.size() == 0) {
			paramsLine = name + "参数共0个 ";
		} else {
			paramsLine = name + "参数共" + params.size() + "个: ";
			for (int i = 0; i < params.size(); i++) {
				String param = params.get(i);
				paramsLine += "[" + param + "]";
			}
		}
		sqlLine += paramsLine;

		long interval = System.currentTimeMillis() - sTime;
		int sec = (int) (interval / 1000);
		String runTime = "" + sec;
		if (sec > 9) {
			runTime = "大于9";
		}
		sqlLine += " 处理笔数 ： [" + rows + "] 执行时间：[" + runTime + "秒(" + interval + "毫秒)]";
		logger.info(sqlLine);
	}

	/**
	 * 
	 * @Description:提交事务
	 * @author ganfeng
	 * @date 2014-3-10 下午05:34:16
	 * @return
	 */
	public boolean commit(){
		try {
			conn.commit();
			return true;
		} catch (SQLException e) {
			return false;
		}
	}
	
	/**
	 * 
	 * @Description:事务回滚
	 * @author ganfeng
	 * @date 2014-3-10 下午05:35:54
	 * @return
	 */
	public boolean rollbace(){
		try {
			conn.rollback();
			return true;
		} catch (SQLException e) {
			return false;
		}
	}
	
	/**
	 * 
	 * @Description:释放
	 * @author ganfeng
	 * @date 2014-3-10 下午05:38:19
	 */
	public void close(){
		if (conn != null) {
			try {
				conn.rollback();
				conn.close();
			} catch (Throwable e) {
			}
		}
	}
	
	protected void finalize() throws Throwable {
		close();
	}

}
